Sample Excel Prep Sheet Formulae

Kira - July 8, 2023

Hey all,

Not sure if this would be helpful to anyone, but I didn’t see a similar post in the forum already, so I figured I’d post in anyway just in case it is. 

As lovely as The Prepared’s built-in kit builder is, I love the flexibility and computing power of Excel. You can automate calculations, color code, sort, annotate… really anything short of heavy statistics (and, fingers crossed, that might change someday). It’s a tool that’s as simple and as complex as you need it to be, really. Sadly, even a lot of graduates in STEM don’t know the basics of Excel. I’m far from a master of statistics, but even a few basic tricks in Excel can literally save you hours of manual formatting and calculating. 

Okay, on to the sheet! Here’s a screenshot of what your final table will look like: 

I like to start with a visual bc it helps me stay on track if I know what I’m working towards. I did try uploading the sheet itself, but it looks like that’s not supported on the forum (please correct me if I’m wrong). 

Your first row in your shit is your headers. Those are gonna stay untouched during any other modifications we make later. I have these headers (in order) for most of my sheets: Item, # of item, Priority, In Kit?, oz, Est. Cost, Tot. Cost, Link, Description, Kit Notes. If you want to format these to make them visually distinct from the rest of the sheet, what you want to do is highlight the first row (I like to click on the 1 on the side to highlight the whole row, but you can also drag a box over all your headers), click on the Home tab, click on the Cell Styles box, and then select your favorite header formatting. My headers are still in the standard formatting because I prefer that look. This is a cosmetic choice really, but anything that makes this more clear for you while reading is worthwhile. 

Next we’re going to look column by column. Your first column is your list of items by name. Your second column is the number you need (or have).

The third column is the priority. Priority is how I decide what I really need in limited space/on a limited budget. I break this into three ranks a la TP’s first aid kit, but you can use as many ranks as you want. You will notice this is broken into colors! Instead of manually highlighting each cell, what I do is I highlight the whole row, click the Home tab up top, and then click Conditional Formatting. From there, I go into Highlight Cell Rules, then Equal To. You should have a little pop-up box now. Under “format cells that are equal to”, type 1. In the drop down menu next to it, pick your favorite formatting OR hit the custom format at the bottom of the list (this is what I do). You can know pick your favorite color and/or pattern for the fill. I used the three middle values for the blue, but you can do whatever helps you visualize this. The same steps are used to highlight In Kit?, except you need to keep in mind that that is case sensitive (I believe) when using letters, so you need to stick with either N or n (or just enter two rules for N and n for formatting). 

In Kit? is just whether something is already in your kit or not. This is usually a Y/N value, but I include a third value S (for Some) in cases when I have an item that’s heterogenous. For example, my BOB sheet says clothes, which I have marked S because I have some clothes but not all the clothes I plan to include. Usually when I have a value marked S, I include a tab for specifically that value. E.g., I have my BOB tab, and I have a clothes tab that breaks down each piece of clothing into a Y/N. It’s just easier to read the BOB sheet without so much clutter. 

Oz is ounces of each item. I am lazy and have not bothered to actually weigh out my bag because it is small enough that it really can’t get to heavy for me, but eventually I’m going to fill this out. I know a lot of people here are more on top of weight for their packs, though! 

Est. cost is estimated cost per item. Total cost is the total cost of how much that row is. So, if you need 10 $1 items, Est. Cost is 1 and Total Cost is 10. But wait! What if you have your 10 items already? In that case, total cost is 0 no matter what. Here’s how we make that happen: =IF(D2=”N”,B2*F2,0)  Paste the formula directly below the header, hover your cursor over the bottom right corner until it looks like a black cross, and then drag the formula into every cell you want filled. Now you’ll see the true total cost per column. 

Link is just a hyperlink to a site if I know where I’ll be buying an item. If I’m getting it from the local grocery store, that just never gets filled in. Description is a description of the item in detail so I know which one to get if it’s a little vague, like maybe you want a specific kind of paracord, and kit notes is notes about what I have in my kit/ how I’m storing it/ really anything. You could probably make this one Notes column, but I like breaking it out into two. 

Okay, almost done. Now we head to the bottom of the sheet. We have two more things to fill in: total cost and total weight. These are the same formula, basically. Total cost is =SUM(G2:G4)  and total weight is =SUM(E2:E4)  (they just add up different columns). These don’t account for tax or rounding errors, but they should give you a good idea of the total investment for your kit. 

Finally, I like to sort my kit by priority rank. You can do it by alphabetical order or cost or weight or whatever works for you; the way you do it is the same. You highlight your whole table EXCEPT for your headers and for the total cost and total weight rows at the very bottom (but including the Total Cost column in the sheet). Then you head to the Data tab up top. Then you click the box that says sort. Now you have a pop up with three boxes. In your first box, pick the column you want to sort by. For me, that’s priorities. In your second box, you probably want to stick to cell values. In your third box, you select how you want those values ordered. For me, that’s smallest to largest. Now your sheet is ordered based on priority! 

Congrats! You now have an automated, color coded Excel prepping sheet. If anything was confusing, or if you have more Excel related questions, I would be super happy to clarify/explain! I’m a total nerd about this stuff (clearly).